import frappe
from frappe.desk import search
from frappe.model.base_document import get_controller
from frappe.utils import cint, cstr
import json
import re
from frappe.permissions import has_permission
from frappe.utils.caching import redis_cache
from pypika.terms import PseudoColumn

@frappe.whitelist()
def search_widget(
    doctype: str,
    txt: str,
    query: str | None = None,
    searchfield: str | None = None,
    start: int = 0,
    page_length: int = 10,
    filters: str | None | dict | list = None,
    filter_fields=None,
    as_dict: bool = False,
    reference_doctype: str | None = None,
    ignore_user_permissions: bool = False,
):
    """搜索框功能，支持达梦数据库
    
    Args:
        doctype: 文档类型
        txt: 搜索文本
        query: 自定义查询函数
        searchfield: 搜索字段
        start: 起始位置
        page_length: 每页长度
        filters: 过滤条件
        filter_fields: 额外的过滤字段
        as_dict: 是否返回字典格式
        reference_doctype: 引用的文档类型
        ignore_user_permissions: 是否忽略用户权限
    """
    start = cint(start)

    if isinstance(filters, str):
        filters = json.loads(filters)

    if searchfield:
        search.sanitize_searchfield(searchfield)

    if not searchfield:
        searchfield = "name"

    standard_queries = frappe.get_hooks().standard_queries or {}

    if not query and doctype in standard_queries:
        query = standard_queries[doctype][-1]

    if query:
        search.is_whitelisted(frappe.get_attr(query))
        return frappe.call(
            query,
            doctype,
            txt,
            searchfield,
            start,
            page_length,
            filters,
            as_dict=as_dict,
            reference_doctype=reference_doctype,
        )

    meta = frappe.get_meta(doctype)

    if isinstance(filters, dict):
        filters_items = filters.items()
        filters = []
        for key, value in filters_items:
            filters.append(search.make_filter_tuple(doctype, key, value))

    if filters is None:
        filters = []
    or_filters = []

    # build from doctype
    if txt:
        field_types = {
            "Data",
            "Text",
            "Small Text",
            "Long Text",
            "Link",
            "Select",
            "Read Only",
            "Text Editor",
        }
        search_fields = ["name"]
        if meta.title_field:
            search_fields.append(meta.title_field)

        if meta.search_fields:
            search_fields.extend(meta.get_search_fields())

        for f in search_fields:
            fmeta = meta.get_field(f.strip())
            if not meta.translated_doctype and (f == "name" or (fmeta and fmeta.fieldtype in field_types)):
                or_filters.append([doctype, f.strip(), "like", f"%{txt}%"])

    if meta.get("fields", {"fieldname": "enabled", "fieldtype": "Check"}):
        filters.append([doctype, "enabled", "=", 1])
    if meta.get("fields", {"fieldname": "disabled", "fieldtype": "Check"}):
        filters.append([doctype, "disabled", "!=", 1])

    # format a list of fields combining search fields and filter fields
    fields = search.get_std_fields_list(meta, searchfield or "name")
    if filter_fields:
        fields = list(set(fields + json.loads(filter_fields)))

    # 根据数据库类型使用不同的标识符引号
    if frappe.db.db_type == "dm":
        formatted_fields = [f'"tab{meta.name}"."{f.strip()}"' for f in fields]
        # Insert title field query after name
        if meta.show_title_field_in_link and meta.title_field:
            formatted_fields.insert(1, f'"tab{meta.name}".{meta.title_field} as "label"')
    else:
        formatted_fields = [f"`tab{meta.name}`.`{f.strip()}`" for f in fields]
        if meta.show_title_field_in_link and meta.title_field:
            formatted_fields.insert(1, f"`tab{meta.name}`.{meta.title_field} as `label`")

    order_by_based_on_meta = search.get_order_by(doctype, meta)
    order_by = f'"tab{doctype}".idx desc, {order_by_based_on_meta}' if frappe.db.db_type == "dm" else f"`tab{doctype}`.idx desc, {order_by_based_on_meta}"

    if not meta.translated_doctype:
        _txt = frappe.db.escape((txt or "").replace("%", "").replace("@", ""))
        
        # 根据数据库类型使用不同的相关性计算
        if frappe.db.db_type == "dm":
            # 达梦数据库使用 POSITION 函数替代 LOCATE
            _relevance = f'(1 / nullif(POSITION({_txt} in "tab{doctype}"."name"), 0))'
            formatted_fields.append(f'{_relevance} as "_relevance"')
            # 达梦数据库的排序语法
            order_by = f'COALESCE("_relevance", -9999) desc, {order_by}'
        elif frappe.db.db_type == "mariadb":
            _relevance = f"(1 / nullif(locate({_txt}, `tab{doctype}`.`name`), 0))"
            formatted_fields.append(f"{_relevance} as `_relevance`")
            order_by = f"ifnull(_relevance, -9999) desc, {order_by}"
        elif frappe.db.db_type == "postgres":
            _relevance = f"(1 / nullif(position({_txt} in `tab{doctype}`.`name`), 0))"
            formatted_fields.append(f"{_relevance} as `_relevance`")
            order_by = f"{len(formatted_fields)} desc nulls last, {order_by}"

    ignore_permissions = doctype == "DocType" or (
        cint(ignore_user_permissions)
        and has_permission(
            doctype,
            ptype="select" if frappe.only_has_select_perm(doctype) else "read",
            parent_doctype=reference_doctype,
        )
    )

    values = frappe.get_list(
        doctype,
        filters=filters,
        fields=formatted_fields,
        or_filters=or_filters,
        limit_start=start,
        limit_page_length=None if meta.translated_doctype else page_length,
        order_by=order_by,
        ignore_permissions=ignore_permissions,
        reference_doctype=reference_doctype,
        as_list=not as_dict,
        strict=False,
    )

    if meta.translated_doctype:
        values = (
            result
            for result in values
            if any(
                re.search(f"{re.escape(txt)}.*", _(cstr(value)) or "", re.IGNORECASE)
                for value in (result.values() if as_dict else result)
            )
        )

    values = sorted(values, key=lambda x: search.relevance_sorter(x, txt, as_dict))

    # remove _relevance from results
    if not meta.translated_doctype:
        if as_dict:
            for r in values:
                r.pop("_relevance", None)
        else:
            values = [r[:-1] for r in values]

    return values
